with zipfile.ZipFile(r"C:\Users\shaphanng\Dropbox\TaxSubsidy\replication\patent\most_sim.zip") as z:
    with z.open("most_sim.json") as f:
        data = f.read()

d= data.decode('ASCII').split('\n')
data =''

d1 = d[:-1]
foc_pat_all = [json.loads(j)[0] for j in d1]

localpat_df=pd.read_csv (r'C:\Users\shaphanng\Dropbox\TaxSubsidy\replication\stata working files\localfirmpat3.csv')
subpat_df=pd.read_csv (r'C:\Users\shaphanng\Dropbox\TaxSubsidy\replication\stata working files\subfirmpat3_all.csv')

localpat_df["nr_pt"] = localpat_df["nr_pt"].values.astype(str)
subpat_df["nr_pt"]= subpat_df["nr_pt"].values.astype(str)

misspat_list =[]
localsubpat_df = pd.DataFrame()
i=0
while i < len(localpat_df):
    localpat = localpat_df['nr_pt'][i]
    localpat_df1 = localpat_df[localpat_df['nr_pt']==localpat]
    
    try:
        pat_index = foc_pat_all.index(localpat)
        d_list = json.loads(d[pat_index])
        patnum = d_list[0]
        sim_patnum = [i[0] for i in d_list[1]]
        simscore = [i[1] for i in d_list[1]]
        patnum = [patnum]*len(d_list[1])
        
        patnum_df = pd.DataFrame(patnum, columns = ['patnum'])
        sim_patnum_df = pd.DataFrame(sim_patnum, columns = ['sim_patnum'])
        simscore_df = pd.DataFrame(simscore, columns = ['simscore'])
        simpat_df = pd.concat([patnum_df, sim_patnum_df, simscore_df], axis =1)
        
        ##merge simpat_df to localpat_df1
        localpat_merge = localpat_df1.merge(simpat_df, left_on = 'nr_pt', right_on = 'patnum')
        localsubpat_merge = localpat_merge.merge(subpat_df, left_on = ['sim_patnum', 'foc_cfips'], right_on = ['nr_pt', 'foc_cfips'])
        localsubpat_merge1 = localsubpat_merge[['treat_year_x', 'foc_cfips', 'nr_pt_x', 'sim_patnum', 'simscore']] 
        localsubpat_merge1 = localsubpat_merge1.rename(columns = {'treat_year_x': 'treat_year', 'nr_pt_x': 'nr_pt'}) 
        
        localsubpat_df = pd.concat([localsubpat_df, localsubpat_merge1])
    
    except ValueError:
        misspat_list.append(localpat)
    
    i=i+1

localsubpat_df.to_csv(r'C:\Users\shaphanng\Dropbox\TaxSubsidy\replication\stata working files\localsub_sim.csv')
